#!/usr/bin/python
# File:          connect.py
# Author:        Yaniv Eylon
# Date:          May-6-2007
# Handle the connection to mysql DB

#import reports
import MySQLdb as conn
import sys, os, ConfigParser, time

import smtplib
import os
from email.MIMEMultipart import MIMEMultipart
from email.MIMEBase import MIMEBase
from email.MIMEText import MIMEText
from email.Utils import COMMASPACE, formatdate
from email import Encoders

def send_mail(send_from, send_to, subject, text, files=[], server="localhost"):
    assert type(send_to)==list
    assert type(files)==list

    msg = MIMEMultipart()
    msg['From'] = send_from
    msg['To'] = COMMASPACE.join(send_to)
    msg['Date'] = formatdate(localtime=True)
    msg['Subject'] = subject

    msg.attach( MIMEText(text) )

    for f in files:
        try:
            part = MIMEBase('application', "octet-stream")
            part.set_payload( open(f,"rb").read() )
            Encoders.encode_base64(part)
            part.add_header('Content-Disposition', 'attachment; filename="%s"' % os.path.basename(f))
            msg.attach(part)
        except:
            pass

    smtp = smtplib.SMTP(server)
    smtp.sendmail(send_from, send_to, msg.as_string())
    smtp.close()


class mysql:
    """
    Manage connection to qa DataBase
    """

    def __init__(self):
        """
        Init class parameters
        Optional Parameters:
            host    = MySQL host
            user    = user with premition to MySQL DB
            passwd  = user's password to MySQL DB
            db      = selected DB to work with
        +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        All of the above should be read from mysql.conf file!!!!!
        +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        """
        try:
            # Read DB info from mysql.conf file
	    confFile = "mysql.conf"
            ini_obj=ConfigParser.ConfigParser()
            ini_obj.read(confFile)

            #Init db params
            self.db     = ini_obj.get('DB','db')
            self.test_db= ini_obj.get('DB','tests_db')
            self.user   = ini_obj.get('USER','user')
            self.passwd = ini_obj.get('PASS','passwd')
            self.host   = ini_obj.get('HOST','host')
	    del(ini_obj)
        except ConfigParser.NoSectionError,e:
            print str(e)
        except ConfigParser.ParsingError,e:
            print str(e)
        except:
            pass

        # Class parameters
        self.connection_id=False
        self.test_exec_id = 0
        self.testId     = None
        self.listId     = None
        self.iterationId= None
        self.setup_name = None
        #self.result=[]
        self.cursor=False
        self.debug = 0
        # open the connection to the DB
        self.connect();

    def setParams(self, setup):
        self.setup_name = setup

#######################
    def connect (self):
        """
        open a connection to the DB
        Save connection id in self.connection_id
        Interface method
        ================
        """
        try:
            self.connection_id = conn.connect(self.host , self.user , self.passwd , self.db )
            return self.connection_id

        except conn.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            return False
###########################
    def query(self,sql=''):
        """
        Run a MySQL query on the DB
        for class internal use
        ======================
        Parameters:
            sql = a valid sql string
        """
        if self.debug:
            print "SQL: " + sql
        try:
            self.cursor = self.connection_id.cursor(conn.cursors.DictCursor)
            self.cursor.execute( sql )
            self.result = self.cursor.fetchall()
            return self.result

        except self.cursor.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            return False
        except conn.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            return False

##############################
    def closeConnection(self):
        """
        Close the connection to the DB
        Interface method
        ================
        """
        try:
            self.connection_id.close()
        except conn.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            return False
##########################
    def showResult (self):
        """
        Print on the screen the result from a select query
        the values are seperated with commas and each row from the DB comes in a line
        Interface method
        ================
        """
        row = self.result
        for line in row:
            toPrint=''
            for value in line :
                toPrint = toPrint + "," + str(value)
            print toPrint
############################
    def affected_rows(self):
        """
        Return the number of efected rows after a query is done on the DB
        if no rows was affected return None
        Interface method
        ================
        """
        try:
            return self.cursor.rowcount

        except self.cursor.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            return False
#####################
    def lastId(self):
        """
        return the last row id (key) after insert/update query
        if no operation was made and no cursor was created an error pops
        for class internal use
        ======================
        """
        try:
            return self.cursor.lastrowid
        except self.cursor.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            return False
############################################
    def getRowCount(self, tblName, col='a'):
        """
        count the number of rows at a given column
        Parameters:
            tblName =   name of the table to run the query
            col     =   column name
            !!! Null values are counted too !!!
        Interface method
        ================
        """
        sql="SELECT count("+col+") FROM " + self.db + "." + tblName
        count=self.query(sql);
        return str(count)
#############################################
    def fileToTable(self, fileName, tblName):
        """
        puts a CSV file in to a table very fast transaction at one hit
        Parameters:
        fileName    =   the file path and name
        tblName     =   table to be inserted to
        !!! not all values should be in the file for all column of the table ==> return a worning MSG
        Interface method
        ================
        """
        #sql="LOAD DATA INFILE " + ' "' + str(fileName) + '" ' + " INTO TABLE " + self.db + "." + tblName + " (" + colName + ") WHERE id="+ str(self.testId) +";";

        sql="LOAD DATA INFILE " + ' "' + str(fileName) + '" ' + " IGNORE INTO TABLE " + self.db + "." + tblName + " FIELDS TERMINATED BY " + '"," ';
        self.query(sql);
#######################################################
    def fileToColumn(self, fileName, tblName, colName):
        """
        puts a file in to a single column cell at a table
        Parameters:
        fileName    =   the file path and name
        tblName     =   table to be inserted to
        colName     =   column name at the table to insert the file
        Interface method
        ================
        """
        if(tblName=='tests'):
            whichId = self.testId
        else:
            whichId = self.listId

        fileStr=file(fileName).read();
        sql = "UPDATE "+ self.db + "." + tblName + " set "+colName+"=\""+fileStr+"\" where id=" + str(whichId)+";"
        self.query(sql);
##########################################################
    def testStartTime(self, testName, iterations, header, header_types='', build='', revision='', version='', optParams='', statParams='', resetTestId=False):
        """
        Log in to the DB the tests start time and other tests parameters
        Parameters:
           testName     =   self.test_name
           iterations   =   self.iterations
           header       =   header line from the test
           headerTypes  =   type of the values in the header line
        Interface method
        ================
        """
	if resetTestId == True:
		self.testId=None
        if self.testId==None:
            if(self.setup_name<>None):
                # first get the setup id from the config file
                self.setupId(self.setup_name)
                row=self.result
                try:
                    for line in row:
                        self.setup=line["setup_id"]
                except:
                    self.setup=''
            if self.listId==None:
                self.listId = '-1'

            self.test_start_time = time.strftime("%Y-%m-%d %X")

            sql = "INSERT INTO "+ str(self.db) + ".tests set test_name='"+str(testName)+\
                "',start_time='" + str(self.test_start_time) + "' , iterations="+str(iterations)+\
                ",test_header='" + str(header) + "'" + ",build='"+ str(build) +"'"+\
                ",sw_revision='" + str(revision) +"'"+",sw_version='"+ str(version) +"',"+\
                "setup_id='"+ str (self.setup) + "',"+\
                "run_list_id='"+str(self.listId)+"',"+\
                "opt_params=\""+str(optParams)+"\","+\
                "mand_params='"+str(statParams)+"';";

            self.query(sql);
            self.testId=self.lastId();
        else:
            sql = "update "+ self.db + ".tests set test_name='"+testName+\
                "',start_time='" + str(self.test_start_time) + "', iterations="+str(iterations)+\
                ",test_header='"+header+"'" + ",build='"+ build +"'"+\
                ",sw_revision='"+revision+"'"+",sw_version='"+version+"'" +\
                " WHERE id="+str(self.testId)+";";

            self.query(sql);

        # create a new table for the new added test
        self.createTableIfNotFound(testName, header, header_types)
#########################

    def createTableIfNotFound(self, tblName, header, header_type=''):
        """
        Check if table by name of the tblName can by found in the DB if not create it,
        and enter new feilds by header of the type header_type
        Parameters:
            tblName     =   the name of the tests to be exec
            header      =   table feilds to create
            header_type =   feilds type ==> int, bool, real, str
        for class internal use
        ======================
        """
        self.testTableName = tblName
        # if the test has old convention of passing parameters
        if (header.find(':') == -1 ):
            # convert strings to lists
            header_line = list(header.split(','))
            header_type = list(header_type.split(','))

        else:
            #new param convention 'head1:str,head2:int,head3:text,head4,real'
            newHead = list(header.split(','))
            newHeader = []
            newHeaderType = []
            for hd in newHead:
                temp =hd.split(':')
                newHeader.append(temp[0])
                newHeaderType.append(temp[1])
            header_line = newHeader
            header_type = newHeaderType

	# get MySQL data types
        mysql_header_type=self.convertToMysqlType(header_type)

        # Check if both list has the same length
        header, header_type=self.isEquValTypeArr(header_line, mysql_header_type)

        sql = "CREATE TABLE IF NOT EXISTS " +self.test_db+"."+tblName+\
              " ( test_id INTEGER NOT NULL, ";

        # Dynamicaly build the sql query
        for head in header_line:
            ind = header.index(head)
            str_head=str(head).strip().replace(" ","_").replace("->","_").replace(".","_")
            sql += " "+str_head+" " + mysql_header_type[ind]

##        # make 20 more feilds for reserve for each table
##        i=0
##        while i<20 :
##            sql += "UnknownValue"+str(i)+ " VARCHAR(100) , "
##            i += 1

        sql += "INDEX test_id(`test_id`)) ENGINE = MyISAM;"

        self.query(sql);


####################################
    def convertToMysqlType(self, header_type):
        """
        Get the list of types from the user and convrt them in to MySQL type format
        Parameters:
           header_type      =   type parameters list from user
        formats:
            time    ==>     TIME         NOT NULL
            str     ==>     VARCHAR(100) NOT NULL
            bool    ==>     BOOLEAN      NOT NULL
            real    ==>     REAL         NOT NULL
            int     ==>     INTEGER      NOT NULL
	    text    ==>     TEXT         NOT NULL
        Return Val:
            new_header_type     =   New MySQL value types list
        for class internal use
        ======================
        """
        new_header_type=[]
        for Htype in header_type :
            Htype = str(Htype).strip()
            if Htype == 'time' :
               new_header_type.append(' TIME NOT NULL, ')

            elif Htype == 'str' :
               new_header_type.append(' VARCHAR(255) NOT NULL, ')

            elif Htype == 'bool' :
               new_header_type.append(' BOOLEAN NOT NULL, ')

            elif Htype == 'real' :
               new_header_type.append(' REAL NOT NULL, ')

            elif Htype == 'int' :
               new_header_type.append(' INTEGER UNSIGNED NOT NULL, ')

	    elif Htype == 'text' :
	       new_header_type.append(' TEXT NOT NULL, ')
            else :
                pass

        return new_header_type
###################################################
    def isEquValTypeArr(self, header, header_type):
        """
        make sure that both lists from users have the same parameters if not fix the problems
        Parameters:
            header      =   table feilds to create
            header_type =   feilds type ==> int, bool, real, str

        Return Val:
            header, header_type
        for class internal use
        ======================

        """
        if (len (header) <> len(header_type)):
            i=0
            while(len(header)<>len(header_type)):
                if (len(header) > len(header_type)):
                    header_type.append(' VARCHAR(100) NOT NULL,')
                if (len(header) < len(header_type)):
                    header.append(str('val'+str(i)))
                    i += 1
        return header, header_type

######################################################
    def testParameters(self, mand_params, opt_params):
        """
        Log test's constructor parameters
        Parameters:
            mand_params     =   test's constructor mandatory parameters
            opt_params      =   test's constructor optional parameters
        Interface method
        ================
        """
        sql = "insert into "+ self.db + ".tests set mand_params=\""+str(mand_params)+\
              "\", opt_params=\""+str(opt_params)+"\", run_list_id='"+str(self.listId)+"',"+\
              "setup_id='"+self.setup+"';"
        self.query(sql);
        self.testId=self.lastId();

#############################################
    def testStatus(self, testStatus, log_location=''):
        """
        Set test Status True=0, False=1
        Interface method
        ================
        """
        if testStatus==True : status=0
        else: status=1
        if log_location <>'':
            log_location = str(log_location) + ".tar.gz"
        sql = "update " + self.db + ".tests set test_status='" +str(status)+\
              "' ,log_dir ='"+ str(log_location) .replace(" ","_").replace(":","-")+\
              "' WHERE id='" + str(self.testId) + "';";
        self.query(sql);

        # Log end time
        self.testEndTime();
#############################################
    def testEndTime(self):
        """
        Log tests end time
        Interface method OR for class internal use
        ==========================================
        """
        sql = "update " + self.db + ".tests set end_time=NOW(), test_exec_info_id =  "+ str(self.test_exec_id) + "  WHERE id=" + str(self.testId)
        self.query(sql);


##############################################
    def iterationInfo(self, iteration, start, end, status, info):
        """
        Set iteration information
        Parameters:
            iteration   =   current iteration
            start       =   iteration start time
            end         =   iteration end time
            status      =   iteration status True/False
            info        =   iteration info the same line that goes in to CSV file
        Interface method
        ================
        """
        if status==True : stat=0
        else: stat=1
        sql = "insert into "+ self.db + ".results set test_id='"+str(self.testId)+\
              "', iteration_number='"+str(iteration)+"', start_time='"+start+\
              "', end_time='"+end+"', iteration_status='"+str(stat)+\
              "', iteration_info=\""+info+"\";";
        self.query(sql);
        self.iterationId=self.lastId();

        # add info to test table
        self.iterationInfoToTestTable1(self.testId, info);

#############################################
    def iterationInfoToTestTable(self, testId, info):
        """
        insert the iteration information as collected in the Python test script in to
        the test unique table
        Parameters:
            test_id     =   the connection to the test unique id in the tests table
            info        =   the information line from python test script
        """

        info=list(str(info).split(','))
        str_info=''
        for val in info :
            str_info += " , '"+val+"'"

        sql = "INSERT IGNORE INTO " +self.db + "."+ self.testTableName + " VALUES("+str(self.testId)+str_info + ");"


        self.query(sql);
        self.testTableId=self.lastId();

#############################################
    def iterationInfoToTestTable1(self, testId, info):
        """
        insert the iteration information as collected in the Python test script in to
        the test unique table
        Parameters:
            test_id     =   the connection to the test unique id in the tests table
            info        =   the information line from python test script
        """
        # convert values to be inserted to list
        values = list(str(info).split(','))
        values.insert(0,str(self.testId))

       # Get table column names
        cols   = self.getTableColumnNames();

        str_sql=''
        comma=''

        for key in cols :
            ind=cols.index(key)
            try:
               val=values[ind]

            except:
                val=''

            str_sql += comma + " " + str(key) + " = '" +str(val)+"'"
            comma=','

        sql = "INSERT INTO " +self.test_db + "."+ self.testTableName + " SET "+ str_sql +";"


        self.query(sql);
        self.testTableId=self.lastId();

#############################################
    def getTableColumnNames(self, tbl=''):
        """
        get the testTable column names
        Return Value:
            list of the names
        """
        col_names=[]
        try:
            tbl_name=self.testTableName
        except:
            pass
        if tbl<>'':
            tbl_name=tbl
        sql = "SHOW COLUMNS FROM " +self.test_db + "."+ tbl_name + ";"
        self.query(sql);
        row = self.result
        for line in row:
            col_names.append(line["Field"])

        return col_names


#############################################
    def listStartTime(self, listName, listConfFile):
        """
        Log in to the DB the list of tests to run start time and other list parameters
        Parameters:
           listName     =   the key name of the list
           iterations       =   confi file the list uses
        Interface method
        ================
        """
        # first get the setup id from the config file
        self.setupId(listConfFile)
        row=self.result
        try:
            for line in row:
                self.setup=line["setup_id"]
        except:
            self.setup=''

        sql = "insert into "+ self.db + ".run_lists set list_name='"+listName+\
              "',start_time=NOW(), list_conf_file='"+str(listConfFile)+\
              "';";
        self.query(sql);
        self.listId=self.lastId();
#############################################
    def setupId(self, ConfFile):
        """
        find on which setup the test is running
        by the given name of the configuration file
        Parameters:
            ConfFile    =   configuration file name
        for class internal use
        ======================
        """
        sql = "SELECT setup_id FROM "+ self.db + ".setups s WHERE INSTR('" + ConfFile + "' ,s.setup_name );"
        return self.query(sql);

#############################################
    def listEndTime(self):
        """
        Log tests end time
        Interface method OR for class internal use
        ==========================================
        """
        sql = "update " + self.db + ".run_lists set end_time=NOW() WHERE id=" + str(self.listId)
        self.query(sql);

        self.generateReport()
#############################################
    def generateReport(self, listId='', conf_file="mysql.conf", sendMail='yes'):


        """
        After list has done sent the report to QA Team or to list of TO
        """
        if (listId==''):
           listId=self.listId

        sql_list="""
                    SELECT  r.id, r.list_name, r.start_time, r.end_time, list_conf_file , global_file, os
                    FROM    run_lists r
                    WHERE   id =  """ + str(listId) + ";"

        sql_test="""
                    SELECT  t.id,t.test_name, if (NOT t.test_status,'<font color="Blue">Pass</font>','<font color="Red">Fail</font>') 'status',
                            t.iterations, t.start_time, t.end_time, t.build, t.sw_revision, t.sw_version , t.result_file, t.opt_params
                    FROM    tests t
                    WHERE   run_list_id= """ + str(listId) +\
                    """ ORDER BY status desc;"""

        sql_result="""
                    SELECT  r.iteration_number, if(NOT r.iteration_status,'<font color="Blue">Pass</font>','<font color="Red">Fail</font>') status,
                            r.start_time, r.end_time, r.iteration_info
                    FROM    results r
                    WHERE   test_id = """


        style="STYLE='position:absolute;top:-1000;left:-1000;z-index:2;visibility:hidden;background-color: #f6ecc0;color:black;border-width: 1px;border:thin solid;padding:5px 5px 5px 5px'"

        build_html="""
                    <html>
                        <head>
                            <title>QA Auto Test Report</title>
                        </head>
                        <body>
                        <h2> QA Report For Automatic Tests:  """ + time.strftime('%X %x') + "</h2>"


        table_html="""
                            <table border = '0' width='100%'>
                                <tr width='100%' align='center'>
                                    <th align='left'> List Id <th align='left'> List Name
                                    <th align='left'> List Start Time
                                    <th align='left'> List End Time
                                    <th align='left'> Setup
                                    <th align='left'> Operating System
                                </tr><tr></tr>
                    """



        # Get list information
        list_str=""
        div_str=""
        list_name=""
        theBuild = ""
        list_info=self.query(sql_list)
        for cell in list_info:
            list_name= str(cell['list_name'])
            div_str += "<div ID='list"+str(cell['id'])+"' "+style+\
                       " title='Duoble Click To Close' ondblclick=\"getElementById('list"+str(cell['id'])+"').style.visibility='hidden'\">"+\
                       "<input type='button' name='close"+str(cell['id'])+"' value='Close' title='Click To Close'"+\
                       "       onclick=\"getElementById('list"+str(cell['id'])+"').style.visibility='hidden'\"><br>"+\
                        str(cell['global_file']).replace("\n","<br>")+"</div>"
            list_str += "<tr>"
            list_str += "<td>" + str(cell['id']) + "</td>"+"<td><font color='green'><u> <i>" + str(cell['list_name']) + "</i> </u></td>"+\
                        "<td>" +  str(cell['start_time']) + "</td>" + "<td>" +  str(cell['end_time']) + "</td>" +\
                        "<td>" +  str(cell['list_conf_file']) + "</td>"
            list_str += "<td ><font color='green'><u> <i>"+str(cell['os'])+"</i> </u></td>"

            list_str += "</tr>"

        table_html += list_str
        table_html += """
                      <tr></tr><tr>
                          <th align='left'> Test ID <th align='left'> Test Name <th align='left'> Test Status <th align='left'> Iteration# <th align='left'> Test Start Time <th align='left'> Test End Time
                              <th align='left'> Build <th align='left'> Revision <th align='left'> Version <th align='left'> Test Parameters
                      </tr><tr></tr>
                    """



        # Get test information
        test_info=self.query(sql_test)
        sub=""
        if(test_info):
            for cell in test_info:
                theBuild = str(cell['build'])
                sub="Build: "+ str(cell['build']) + ", Revision: "+str(cell['sw_revision'])+", Version: "+str(cell['sw_version'])
                div_str += "<div ID='test"+str(cell['id'])+"' "+style+\
                           " title='Duoble Click To Close' ondblclick=\"getElementById('test"+str(cell['id'])+"').style.visibility='hidden'\">"+\
                           "<input type='button' name='close"+str(cell['id'])+"' value='Close' title='Click To Close'"+\
                           "       onclick=\"getElementById('test"+str(cell['id'])+"').style.visibility='hidden'\"><br>"+\
                           str(cell['opt_params']).replace("\n","<br>").replace(" ","<br>")+"</div>"

                test_str = "<tr>"
                test_str += "<td>" + str(cell['id']) + "</td>"+ "<td>" + str(cell['test_name']) + "</td>"

                test_str += "<td><a href='#' title=' Click To Open File, \n\r Duoble Click To Close' id='res" +str(cell['id'])+"'"+\
                            "  onclick=\"getElementById('result"+str(cell['id'])+"').style.top = getElementById('res"+str(cell['id'])+"').style.top+getElementById('res"+str(cell['id'])+"').style.height;"+\
                            "                getElementById('result"+str(cell['id'])+"').style.left = getElementById('res"+str(cell['id'])+"').style.left+getElementById('res"+str(cell['id'])+"').style.width;"+\
                            "                getElementById('result"+str(cell['id'])+"').style.visibility='visible';\""+\
                            "  ondblclick =   \"getElementById('result"+str(cell['id'])+"').style.visibility='hidden' \""+\
                            ">"+str(cell['status'])+"</a></td>"


                test_str += "<td>" + str(cell['iterations']) + "</td>" + "<td>" + str(cell['start_time']) + "</td>"+\
                                "<td>" + str(cell['end_time']) + "</td>"+ "<td>" + str(cell['build']) + "</td>"+"<td>" + str(cell['sw_revision']) + "</td>"+\
                                "<td>" + str(cell['sw_version']) + "</td>"

                test_str += "<td><a href='#' title=' Click To Open File, \n\r Duoble Click To Close' id='"+str(cell['id'])+\
                            "' onclick=\"getElementById('test"+str(cell['id'])+"').style.top = getElementById('"+str(cell['id'])+"').style.top+getElementById('"+str(cell['id'])+"').style.height;"+\
                            "                getElementById('test"+str(cell['id'])+"').style.left = getElementById('"+str(cell['id'])+"').style.left+getElementById('"+str(cell['id'])+"').style.width;"+\
                            "                getElementById('test"+str(cell['id'])+"').style.visibility='visible';\""+\
                            "  ondblclick =\"getElementById('test"+str(cell['id'])+"').style.visibility='hidden' \""+\
                            "><font color='blue'> <i>Params</i></font></a></td>"

                table_html += test_str

                # get iteration information from test table
                div_str += "<div ID ='result"+str(cell['id'])+"' " +style+\
                           " title='Duoble Click To Close' ondblclick=\"getElementById('result"+str(cell['id'])+"').style.visibility='hidden'\">"+\
                           "<input type='button' name='close"+str(cell['id'])+"' value='Close' title='Click To Close'"+\
                           "       onclick=\"getElementById('result"+str(cell['id'])+"').style.visibility='hidden'\">"+\
                           "<input type='button' name='csv"+str(cell['id'])+"' value='CSV' title='Click To open csv file'"+\
                           "       onclick=\"alert('Not Yet Implemented')\"><br>"
                result_str=""
                result_info=self.query("SELECT * FROM "+ self.test_db+ "." + str(cell['test_name']) + " WHERE test_id='" + str(cell['id']) + "'")
                if (result_info):
                    table_header=self.getTableColumnNames(str(cell['test_name']))
                    # Build header for div table
                    div_str += " <table> <tr>"
                    for head in table_header :
                        div_str += " <th align='left'>" + head + " </th> "
                    div_str += "</tr>"
                    for cell1 in result_info :
                        div_str += "<tr>"
                        for head in table_header :
                            div_str += "<td>"+str(cell1[head])+ "</td>"
                        div_str += "</tr>"
                    div_str += "</table></div>"

        table_html += "</table>"+div_str+"</body></html>"
        build_html += table_html
        file_str="/tmp/QAutoTest__"+str(list_name)+"__"+str(listId)+".html"
        fl=file(file_str,"w")
        fl.write(build_html)
        fl.close()
        subject=" ==> "+str(sub)+" ==> RunList: " + str(list_name)+ ", Id: "+ str(listId)

        if(sendMail=='yes'):
            # send the report
            self.mailReport(file_str, conf_file, subject, theBuild)
        else:
            return (build_html, str(sub), str(list_name), str(listId))

#############################################
    def mailReport(self, report, conf_file="mysql.conf", mail_subject='', theBuild=''):
        """
        Send the list report to list of ppl from mysql.conf file section=[REPORT]
        Parameters:
            report = html file holds the report
            All parameters for send the mail should be configured from mysql.conf file
        """
        body_text="""QA-Auto-Test-report \n\r Click link to see detailed result \n\r Duoble Click to Close \n\r"""

        try:
            # Read DB info from mysql.conf file
            ini_obj=ConfigParser.ConfigParser()
            ini_obj.read(conf_file)

            #Init db params
            to       = ini_obj.get('REPORT','to')
            sender   = ini_obj.get('REPORT','from')
            server   = ini_obj.get('REPORT','server')
            subject  = ini_obj.get('REPORT','subject')

            del(ini_obj)
        except ConfigParser.NoSectionError,e:
            print str(e)
        except ConfigParser.ParsingError,e:
            print str(e)
        except:
            pass
        if(mail_subject<>''):
            subject += " "+mail_subject
        # send report by mail
        send_mail(send_from=sender,
                  send_to=list(str(to).split(',')),
                  subject=subject ,
                  text=body_text,
                  files=list(str(report).split(' ')),
                  server=server)


#############################################
    def testReport(self, testId, conf_file="mysql.conf", sendMail='yes'):


        """
        After test has done send the report to QA Team or to list of TO
        """

        sql_test="""
                    SELECT  t.id,t.test_name, if (NOT t.test_status,'<font color="Blue">Pass</font>','<font color="Red">Fail</font>') 'status',
                            t.iterations, t.start_time, t.end_time, t.build, t.sw_revision, t.sw_version , t.result_file, opt_params
                    FROM    tests t
                    WHERE   id= """ + str(testId) + ";"

        sql_result="""
                    SELECT  r.iteration_number, if(NOT r.iteration_status,'<font color="Blue">Pass</font>','<font color="Red">Fail</font>') status,
                            r.start_time, r.end_time, r.iteration_info
                    FROM    results r
                    WHERE   test_id = """


        style="STYLE='position:absolute;top:-1000;left:-1000;z-index:2;visibility:hidden;background-color: #f6ecc0;color:black;border-width: 1px;border:thin solid;padding:5px 5px 5px 5px'"

        build_html="""
                    <html>
                        <head>
                            <title>QA Auto Test Report</title>
                        </head>
                        <body>
                        <h2> QA Report For Automatic Tests:  """ + time.strftime('%X %x') + "</h2>"


        table_html="""
                    <table border = '0' width='100%'>
                      <tr>
                          <th align='left'> Test ID <th align='left'> Test Name <th align='left'> Test Status <th align='left'> Iteration# <th align='left'> Test Start Time <th align='left'> Test End Time
                              <th align='left'> Build <th align='left'> Revision <th align='left'> Version <th align='left'> Result File
                      </tr><tr></tr>
                    """



        # Get test information
        test_info=self.query(sql_test)
        sub=""
        div_str=""
        test_name=""
        if(test_info):
            for cell in test_info:
                test_name=str(cell['test_name'])
                sub="Build: "+ str(cell['build']) + ", Revision: "+str(cell['sw_revision'])+", Version: "+str(cell['sw_version'])
                div_str += "<div ID='test"+str(cell['id'])+"' "+style+\
                           " title='Duoble Click To Close' ondblclick=\"getElementById('test"+str(cell['id'])+"').style.visibility='hidden'\">"+\
                           "<input type='button' name='close"+str(cell['id'])+"' value='Close' title='Click To Close'"+\
                           "       onclick=\"getElementById('test"+str(cell['id'])+"').style.visibility='hidden'\"><br>"+\
                           str(cell['opt_params']).replace("\n","<br>").replace(" ","<br>")+"</div>"

                test_str = "<tr>"
                test_str += "<td>" + str(cell['id']) + "</td>"+ "<td>" + str(cell['test_name']) + "</td>"

                test_str += "<td><a href='#' title=' Click To Open File, \n\r Duoble Click To Close' id='res" +str(cell['id'])+"'"+\
                            "  onclick=\"getElementById('result"+str(cell['id'])+"').style.top = getElementById('res"+str(cell['id'])+"').style.top+getElementById('res"+str(cell['id'])+"').style.height;"+\
                            "                getElementById('result"+str(cell['id'])+"').style.left = getElementById('res"+str(cell['id'])+"').style.left+getElementById('res"+str(cell['id'])+"').style.width;"+\
                            "                getElementById('result"+str(cell['id'])+"').style.visibility='visible';\""+\
                            "  ondblclick =   \"getElementById('result"+str(cell['id'])+"').style.visibility='hidden' \""+\
                            ">"+str(cell['status'])+"</a></td>"


                test_str += "<td>" + str(cell['iterations']) + "</td>" + "<td>" + str(cell['start_time']) + "</td>"+\
                                "<td>" + str(cell['end_time']) + "</td>"+ "<td>" + str(cell['build']) + "</td>"+"<td>" + str(cell['sw_revision']) + "</td>"+\
                                "<td>" + str(cell['sw_version']) + "</td>"

                test_str += "<td><a href='#' title=' Click To Open File, \n\r Duoble Click To Close' id='"+str(cell['id'])+\
                            "' onclick=\"getElementById('test"+str(cell['id'])+"').style.top = getElementById('"+str(cell['id'])+"').style.top+getElementById('"+str(cell['id'])+"').style.height;"+\
                            "                getElementById('test"+str(cell['id'])+"').style.left = getElementById('"+str(cell['id'])+"').style.left+getElementById('"+str(cell['id'])+"').style.width;"+\
                            "                getElementById('test"+str(cell['id'])+"').style.visibility='visible';\""+\
                            "  ondblclick =\"getElementById('test"+str(cell['id'])+"').style.visibility='hidden' \""+\
                            "><font color='blue'> <i>Params</i></font></a></td>"

                table_html += test_str

                # get iteration information from test table
                div_str += "<div ID ='result"+str(cell['id'])+"' " +style+\
                           " title='Duoble Click To Close' ondblclick=\"getElementById('result"+str(cell['id'])+"').style.visibility='hidden'\">"+\
                           "<input type='button' name='close"+str(cell['id'])+"' value='Close' title='Click To Close'"+\
                           "       onclick=\"getElementById('result"+str(cell['id'])+"').style.visibility='hidden'\">"+\
                           "<input type='button' name='csv"+str(cell['id'])+"' value='CSV' title='Click To open csv file'"+\
                           "       onclick=\"alert('Not Yet Implemented')\"><br>"
                result_str=""
                result_info=self.query("SELECT * FROM "+ self.test_db+ "." + str(cell['test_name']) + " WHERE test_id='" + str(cell['id']) + "'")
                if (result_info):
                    table_header=self.getTableColumnNames(str(cell['test_name']))
                    # Build header for div table
                    div_str += " <table> <tr>"
                    for head in table_header :
                        div_str += " <th align='left'>" + head + " </th> "
                    div_str += "</tr>"
                    for cell1 in result_info :
                        div_str += "<tr>"
                        for head in table_header :
                            div_str += "<td>"+str(cell1[head])+ "</td>"
                        div_str += "</tr>"
                    div_str += "</table></div>"

        table_html += "</table>"+div_str+"</body></html>"
        build_html += table_html
        file_str="/tmp/QAutoTest__"+str(test_name)+"__"+str(testId)+".html"
        fl=file(file_str,"w")
        fl.write(build_html)
        fl.close()
        subject=" ==> "+str(sub)+" ==> Test Name: " + str(test_name)+ ", Id: "+ str(testId)

        if(sendMail=='yes'):
            # send the report
            self.mailReport(file_str, conf_file, subject)
        else:
            return (build_html, str(sub), str(test_name), str(testId))



###############################################3

# create a global db object for session
# do not remove this line
db = mysql();

